 IF EXISTS (
	SELECT *
	FROM sysobjects
	WHERE id = object_id(N'dbo.spFdsNRemoveRexOwnedProtocols')
	AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
	
	BEGIN
		
		PRINT 'Dropping spFdsNRemoveRexOwnedProtocols'
        DROP PROCEDURE dbo.spFdsNRemoveRexOwnedProtocols
        
	END

GO
	
/***************************************************************************
* Name:		spFdsNRemoveRexOwnedProtocols
* Purpose:	This procedure removes all the protocols in the Insight Asset database except protocols
*			submitted via the web. Additionally, all associated data to those protocols are removed as well. 
*           This procedure retrieves all candidate protocols that should be removed and delegates calls to 
*           another sproc that takes the protocol id and removes all related information aobut the protocol
*           from the Asset database.
*
* PARAMETERS
* Name			Description					
* -------------		-----------------------------------------------------------
* n/a
*
* ERRORS USED		Description
* -------------		-----------------------------------------------------
* n/a
*
* RETURN VALUE
* Value    			Description					
* --------------	------------------------------------------------------
*  @_exitStatus		An exit status of either true or false
***************************************************************************/
CREATE PROCEDURE [dbo].[spFdsNRemoveRexOwnedProtocols]
AS
BEGIN
	DECLARE	@EXIT_FAILURE			BIT
	DECLARE	@EXIT_SUCCESS			BIT
	DECLARE @_protocolId	INTEGER
	DECLARE @_deleteStatus	BIT
	SET	@EXIT_FAILURE 			= 0
	SET	@EXIT_SUCCESS			= 1


	SET NOCOUNT ON
	

	DECLARE RexOwnedProtocols CURSOR LOCAL FAST_FORWARD FOR
		SELECT  id FROM PrtProtocol
	    WHERE  id NOT IN ( --- Get submitted protocols.
			SELECT id  from PrtProtocol
				WHERE prpProposalId is not null
				AND title not like '%test%'
				AND id not in ( 12336, 12497, 12498, 12502)
				AND title  not in ('Awrighty,final look now', 'Phil''s Application', 'New app!! Hooray!!')
		 )
	
	BEGIN TRANSACTION spFdsNRemoveRexOwnedProtocols

	OPEN RexOwnedProtocols
	FETCH NEXT FROM RexOwnedProtocols INTO @_protocolId
	WHILE @@FETCH_STATUS = 0
  	BEGIN
	
			-- get the next unformatted folder number
			EXEC @_deleteStatus = spFdsNCleanupObsoleteProtocol  @_protocolId
			
			IF @_deleteStatus  = 0 GOTO CRITICAL_ERROR_HANDLER
		
			-- fetch the next protocol ID
			FETCH NEXT FROM RexOwnedProtocols INTO @_protocolId
			
	END

	CLOSE RexOwnedProtocols
	DEALLOCATE RexOwnedProtocols
	
	SET NOCOUNT OFF
	
	COMMIT TRANSACTION spFdsNRemoveRexOwnedProtocols
	RETURN @EXIT_SUCCESS

	CRITICAL_ERROR_HANDLER:
	BEGIN
		SET NOCOUNT OFF
		ROLLBACK TRANSACTION spFdsNRemoveRexOwnedProtocols
		RETURN @EXIT_FAILURE
	END

END